package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Connection;

import db.Dbclose;
import db.Dbconn;

import entity4wms.Uaccount;

public final class DAOuaccount {
    static Connection conn =null;
    static PreparedStatement pstmt = null;
    static ResultSet rs = null;

    public static void addUaccount(Uaccount account){
        var str = "INSERT INTO uaccount VALUES(null,?,?)";
        try {
            conn = Dbconn.getConnection();
            pstmt = conn.prepareStatement(str);
            pstmt.setString(1,account.getUpassword() );
            pstmt.setString(2,account.getUname() );
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            Dbclose.close(pstmt,conn);
        }
    }

    public static void deleteUaccount(Uaccount account){
        var str = "DELETE FROM uaccount WHERE uid =?";
        try {
            conn = Dbconn.getConnection();
            pstmt = conn.prepareStatement(str);
            pstmt.setInt(1,account.getUid() );
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            Dbclose.close(pstmt,conn);
        }
    }

    public static void updateUaccount(Uaccount account){
        var str = "UPDATE uaccount SET upassword =?,uname =? WHERE uid =?";
        try {
            conn = Dbconn.getConnection();
            pstmt = conn.prepareStatement(str);
            pstmt.setString(1,account.getUpassword() );
            pstmt.setString(2,account.getUname() );
            pstmt.setInt(3,account.getUid() );
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            Dbclose.close(pstmt,conn);
        }
    }

    public static void queryUaccount() throws SQLException{
        var str = "SELECT * FROM uaccount";
        
        try {
            conn = Dbconn.getConnection();
            pstmt = conn.prepareStatement(str);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"
                    +rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            Dbclose.queryClose(pstmt,rs,conn);
        }
        
    }

    public static ArrayList<Uaccount> checkUaccount(String username) {
        var strSQL = "SELECT * FROM uaccount WHERE uname=?";
        ArrayList<Uaccount> uaccounts = new ArrayList<Uaccount>();
        try {
            conn = Dbconn.getConnection();
            pstmt = conn.prepareStatement(strSQL);
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Uaccount uaccount = new Uaccount();
                uaccount.setUid(rs.getInt(1));
                uaccount.setUname(rs.getString(3));
                uaccount.setUpassword(rs.getString(2));
                uaccounts.add(uaccount);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally{
            Dbclose.queryClose(pstmt,rs,conn);
        }
        
        return uaccounts;

    }

    
}
